package cn.basic.service.batch;

import cn.basic.bean.vo.User;
import com.google.common.base.Stopwatch;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.TimeUnit;

/**
 * @description:
 * @author: ZZZ
 * @time: 2021/3/25 21:15
 */
@Service
public class BatchPsbUserComponent  {
    @Autowired
    private JdbcTemplate jdbcTemplate;


    //全量更新：通过partid 将 表psb_user的openid 更新到psb_user_child 的openid(不为null)
    public Long batchPsbUserOpenIdByPartId(int fqid) {
        return jdbcTemplate.execute(new ConnectionCallback<Long>() {
            @Override
            public Long doInConnection(Connection connection) throws SQLException, DataAccessException {
                Stopwatch stopwatch = Stopwatch.createStarted();
                connection.setAutoCommit(Boolean.TRUE);
                Long totalAll = 0L;

                Integer total = 0;


                PreparedStatement pstmt = connection.prepareStatement(getUserQuerySql());
                ResultSet rs = null;

                Long id = null;
                String openId = null;
                Integer extPartId = null;


                Boolean swich = true;
                while (swich) {
                    total = 0;
                    pstmt.setInt(1, fqid);
                    pstmt.setLong(2, id);
                    rs = pstmt.executeQuery();
                    List<User> userChildList = new ArrayList<>();
                    while (rs.next()) {

                        total = total + 1;
                        //select  a.id,a.openid,a.ext_part_id
                        id = rs.getLong("id");
                        openId = rs.getString("openid");
                        extPartId = rs.getInt("ext_part_id");
                        //查询psb_user_child
                        pstmt = connection.prepareStatement(getUserChildQuerySql());
                        getUserChildList(id, openId, extPartId, pstmt, userChildList);

                    }
                    if (userChildList != null && userChildList.size() > 0) {
                        //更新批处理
                        int[] updateResult = jdbcTemplate.batchUpdate(getUpdateSql(), new BatchPreparedStatementSetter() {
                            @Override
                            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                                //update psb_user_child set openid=?,ext_part_id=? where pid=? and part_id=?
                                preparedStatement.setString(1, userChildList.get(i).getOpenid());
                                preparedStatement.setInt(2, userChildList.get(i).getExtPartId());
                                preparedStatement.setLong(3, userChildList.get(i).getPid());
                                preparedStatement.setInt(4, userChildList.get(i).getPartId());
                            }

                            @Override
                            public int getBatchSize() {
                                return userChildList.size();
                            }
                        });
                        //记录操作数
                        for (int i = 0; i < updateResult.length; i++) {
                            totalAll = totalAll + updateResult[i];
                        }

                    }
                    swich = (total - 500) == 0;
                    if (rs != null) {
                        rs.close();
                    }
                }
                pstmt.close();
                connection.close();

                System.out.println("处理[batch_psb_user_openid_by_partid][{}]完成,共耗时{}s"+ totalAll+" "+stopwatch.elapsed(TimeUnit.SECONDS));
                return totalAll;
            }

            //psb_user_child list
            private List<User> getUserChildList(Long id, String openId, Integer extPartId, PreparedStatement pstmt, List<User> userChildList) throws SQLException {
//                sb.append("select  a.openid,a.ext_part_id,a.pid,a.part_id");
//                sb.append("from psb_user_child a ");
//                sb.append("where a.pid = ? and a.part_id = ? ");
                ResultSet rs = null;
                pstmt.setLong(1, id);
                pstmt.setInt(2, extPartId);
                rs = pstmt.executeQuery();
                while (rs.next()) {

                    //psb_user的openid 更新到psb_user_child 的openid (不为null时)
                    if (rs.getObject("openid") != null) {
                        openId = rs.getString("openid");
                    } else {
                        openId = openId;
                    }

                    //psb_user的ext_part_id 更新到psb_user_child 的ext_part_id (不为null时)
                    if (rs.getObject("ext_part_id") != null) {
                        extPartId = rs.getInt("ext_part_id");
                    } else {
                        extPartId = extPartId;
                    }
                    User user = new User();

                    user.setOpenid(openId);
                    user.setExtPartId(extPartId);
                    user.setPid(rs.getLong("pid"));
                    user.setPartId(rs.getInt("part_id"));

                    userChildList.add(user);
                }
                rs.close();
                return userChildList;

            }

            private String getUserQuerySql() {
                StringBuffer sb = new StringBuffer();
                sb.append("select  a.id,a.openid,a.ext_part_id ");
                sb.append("from psb_user a ");
                sb.append("where a.part_id = ? and a.id > ? ");
                sb.append("order by a.id limit 500 ");
                return sb.toString();
            }

            private String getUserChildQuerySql() {
                StringBuffer sb = new StringBuffer();
                sb.append("select  a.openid,a.ext_part_id,a.pid,a.part_id");
                sb.append("from psb_user_child a ");
                sb.append("where a.pid = ? and a.part_id = ? ");
                return sb.toString();
            }


            //更新sql
            private String getUpdateSql() {
                String sql = "update psb_user_child set openid=?,ext_part_id=? where pid=? and part_id=? ";
                return sql;
            }
        });
    }
   ////增量更新：通过opentime 将 表psb_user的openid 更新到psb_user_child 的openid(不为null)
    public Long batchPsbUserOpenIdByOpenTime(String openTime) {
        boolean matches = openTime.matches("\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}");
        if(!matches){
            System.out.println("日期格式不对"+ openTime);
            return -1L;
        }

        return jdbcTemplate.execute(new ConnectionCallback<Long>() {
            @Override
            public Long doInConnection(Connection connection) throws SQLException, DataAccessException {
                Stopwatch stopwatch = Stopwatch.createStarted();

                connection.setAutoCommit(Boolean.TRUE);
                Long totalAll = 0L;
                Integer total = null;

                PreparedStatement pstmt = connection.prepareStatement(getQuerySql());
                ResultSet rs = null;
                PreparedStatement pstmtUpdate = null;

                String openid = null;
                Long pid = null;
                Integer extpartid=null;
                Integer partid = null;

                Long id = null;


                for (int fqid = 0; fqid < 100; fqid++) {

                    Boolean swich = true;
                    id = 0L;
                    while (swich) {
                        total = 0;
                        pstmt.setInt(1, fqid);
                        pstmt.setLong(2, id);
                        pstmt.setString(3, openTime);
                        rs = pstmt.executeQuery();


                        rs.beforeFirst();
                        List<User> userList = new ArrayList<>();
                        while (rs.next()) {

                            total = total+1;
                            //sb.append("select  a.openid,b.openid openid_child,b.id,b.ext_part_id,b.pid,b.part_id ");
                            id = rs.getLong("id");
                            pid = rs.getLong("pid");
                            partid = rs.getInt("part_id");

                            //psb_user的openid 更新到psb_user_child 的openid (不为null时)
                            if( rs.getObject("openid_child") != null){
                                openid = rs.getString("openid_child");

                            }else{
                                openid = rs.getString("openid");
                            }

                            //psb_user的ext_part_id 更新到psb_user_child 的ext_part_id (不为null时)
                            if( rs.getObject("ext_part_id") != null){
                                extpartid = rs.getInt("ext_part_id");

                            }else{
                                extpartid = partid;
                            }
                            User user = new User();
                            user.setId(id);
                            user.setOpenid(openid);
                            user.setExtPartId(extpartid);
                            user.setPid(pid);
                            user.setPartId(partid);

                            userList.add(user);

                        }
                        if (userList != null && userList.size() > 0) {
                            //更新批处理
                            int[] updateResult = jdbcTemplate.batchUpdate(getUpdateSql(), new BatchPreparedStatementSetter() {
                                @Override
                                public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                                    // update psb_user_child set openid=?,ext_part_id=? where pid=? and part_id=?
                                    preparedStatement.setString(1, userList.get(i).getOpenid());
                                    preparedStatement.setInt(2, userList.get(i).getExtPartId());
                                    preparedStatement.setLong(3, userList.get(i).getPid());
                                    preparedStatement.setInt(4, userList.get(i).getPartId());
                                    preparedStatement.setLong(5, userList.get(i).getId());
                                }

                                @Override
                                public int getBatchSize() {
                                    return userList.size();
                                }
                            });
                            //记录更新数
                            for (int i = 0; i < updateResult.length; i++) {
                                totalAll=totalAll+updateResult[i];
                            }

                        }
                        swich = (total - 1000) == 0;
                        if (rs != null) {
                            rs.close();
                        }
                    }
                }
                pstmt.close();
                connection.close();

                System.out.println("处理[psb_user_opentime][{}]完成,共耗时{}s "+ totalAll+" "+stopwatch.elapsed(TimeUnit.SECONDS));
                return totalAll;
            }

            //查询sql
            private String getQuerySql() {
                StringBuffer sb = new StringBuffer();
                sb.append("select  a.openid,b.openid openid_child,b.id,b.ext_part_id,b.pid,b.part_id ");
                sb.append("from psb_user a,psb_user_child b ");
                sb.append("where a.id=b.pid and a.ext_part_id=b.part_id ");
                sb.append("and a.ext_part_id = ? and b.id > ? and a.op_time > ? ");
                sb.append("order by b.id limit 1000");
                return sb.toString();
            }

            //更新sql
            private String getUpdateSql() {
                String sql = "update psb_user_child set openid=?,ext_part_id=? where pid=? and part_id=? and id=?";
                return sql;
            }
        });
    }
}
